1 Imports System.Data.SqlClient
2 Public Class frmLocation
3
4 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
5 Me.Close()
6 End Sub
7 Sub Reset()
8 txtLocation.Text = ""
9 btnSave.Enabled = True
10 btnDelete.Enabled = False
11 btnUpdate.Enabled = False
12 txtLocation.Focus()
13 End Sub
14 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
15 Reset()
16 End Sub
17
18 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
19 If txtLocation.Text = "" Then
20 MessageBox.Show("Please enter Location", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
21 txtLocation.Focus()
22 Return
23 End If
24
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 Dim ct As String = "select LocationName from Location where LocationName=@d1"
29 cmd = New SqlCommand(ct)
30 cmd.Parameters.AddWithValue("@d1", txtLocation.Text)
31 cmd.Connection = con
32 rdr = cmd.ExecuteReader()
33
34 If rdr.Read() Then
35 MessageBox.Show("Location Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
36 txtLocation.Text = ""
37 txtLocation.Focus()
38 If (rdr IsNot Nothing) Then
39 rdr.Close()
40 End If
41 Return
42 End If
43
44 con = New SqlConnection(cs)
45 con.Open()
46
47 Dim cb As String = "insert into Location(LocationName) VALUES (@d1)"
48 cmd = New SqlCommand(cb)
49 cmd.Parameters.AddWithValue("@d1", txtLocation.Text)
50 cmd.Connection = con
51 cmd.ExecuteReader()
52 con.Close()
53 Dim st As String = "added the new Location '" & txtLocation.Text & "'"
54 LogFunc(lblUser.Text, st)
55 MessageBox.Show("Successfully Saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
56 btnSave.Enabled = False
57 Getdata()
58 Catch ex As Exception
59 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
60 End Try
61 End Sub
62
63 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
64 If txtLocation.Text = "" Then
65 MessageBox.Show("Please enter Location", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
66 txtLocation.Focus()
67 Return
68 End If
69
70 Try
71
72 con = New SqlConnection(cs)
73 con.Open()
74
75 Dim cb As String = "Update Location set LocationName=@d1 where LocationName=@d2"
76 cmd = New SqlCommand(cb)
77 cmd.Connection = con
78 cmd.Parameters.AddWithValue("@d1", txtLocation.Text)
79 cmd.Parameters.AddWithValue("@d2", txtLocationName.Text)
80 cmd.ExecuteReader()
81 con.Close()
82 Dim st As String = "updated the Location '" & txtLocation.Text & "'"
83 LogFunc(lblUser.Text, st)
84 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
85 btnUpdate.Enabled = False
86 Getdata()
87 Catch ex As Exception
88 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
89 End Try
90 End Sub
91 Private Sub DeleteRecord()
92
93 Try
94 Dim RowsAffected As Integer = 0
95
96 con = New SqlConnection(cs)
97 con.Open()
98 Dim cl1 As String = "select LocationName from BusCardHolder_Student,Location where BusCardHolder_Student.Location=Location.LocationName and LocationName=@d1"
99 cmd = New SqlCommand(cl1)
100 cmd.Connection = con
101 cmd.Parameters.AddWithValue("@d1", txtLocation.Text)
102 rdr = cmd.ExecuteReader()
103 If rdr.Read Then
104 MessageBox.Show("Unable to delete..Already in use in bus holder[Student] Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
105 If Not rdr Is Nothing Then
106 rdr.Close()
107 End If
108 Exit Sub
109 End If
110 con = New SqlConnection(cs)
111 con.Open()
112 Dim cl2 As String = "select LocationName from BusCardHolder_Staff,Location where BusCardHolder_Staff.Location=Location.LocationName and LocationName=@d1"
113 cmd = New SqlCommand(cl2)
114 cmd.Connection = con
115 cmd.Parameters.AddWithValue("@d1", txtLocation.Text)
116 rdr = cmd.ExecuteReader()
117 If rdr.Read Then
118 MessageBox.Show("Unable to delete..Already in use in bus holder[staff] Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
119 If Not rdr Is Nothing Then
120 rdr.Close()
121 End If
122 Exit Sub
123 End If
124 con = New SqlConnection(cs)
125 con.Open()
126 Dim cq As String = "delete from Location where LocationName=@d1"
127 cmd = New SqlCommand(cq)
128 cmd.Parameters.AddWithValue("@d1", txtLocationName.Text)
129 cmd.Connection = con
130 RowsAffected = cmd.ExecuteNonQuery()
131 If RowsAffected > 0 Then
132 Dim st As String = "deleted the Location '" & txtLocation.Text & "'"
133 LogFunc(lblUser.Text, st)
134 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
135 Getdata()
136 Reset()
137 Else
138 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
139 Reset()
140 End If
141 If con.State = ConnectionState.Open Then
142 con.Close()
143
144 End If
145 Catch ex As Exception
146 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
147 End Try
148 End Sub
149 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
150 Try
151 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
152 DeleteRecord()
153 End If
154 Catch ex As Exception
155 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
156 End Try
157 End Sub
158
159 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
160 Try
161 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
162 txtLocationName.Text = dr.Cells(0).Value.ToString()
163 txtLocation.Text = dr.Cells(0).Value.ToString()
164 btnUpdate.Enabled = True
165 btnDelete.Enabled = True
166 btnSave.Enabled = False
167 Catch ex As Exception
168 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
169 End Try
170 End Sub
171
172 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
173 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
174 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
175 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
176 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
177 End If
178 Dim b As Brush = SystemBrushes.ControlText
179 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
180
181 End Sub
182 Public Sub Getdata()
183 Try
184 con = New SqlConnection(cs)
185 con.Open()
186 cmd = New SqlCommand("SELECT RTRIM(LocationName) from Location order by LocationName", con)
187 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
188 dgw.Rows.Clear()
189 While (rdr.Read() = True)
190 dgw.Rows.Add(rdr(0))
191 End While
192 con.Close()
193 Catch ex As Exception
194 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
195 End Try
196 End Sub
197
198 Private Sub frmLocationName_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
199 Getdata()
200 End Sub
201 End Class
